<?php
class media_model extends CI_Model {
	public function __construct() {
		parent::__construct ();
		$this->db = $this->load->database ( 'default', true );
	}
	public function save_media($media) {
		$this->db->insert ( "media", $media );
		return $this->db->insert_id ();
	}
	public function update_media($media, $media_id) {
		$this->db->where ( "media_id", $media_id );
		$this->db->update ( "media", $media );
		return $this->db->insert_id ();
	}
	public function save_pic($pic, $pic_thumb, $media_id) {
		$data = array (
				"pic" => $pic,
				"pic_thumb" => $pic_thumb,
				"media_id" => $media_id 
		);
		$this->db->insert ( "media_pic", $data );
	}
	public function delete_media_pic($media_id) {
		$sql = "delete from media_pic where media_id ='$media_id'";
		$this->db->query ( $sql );
	}
	public function save_media_env($type, $env_id, $media_id) {
		$data = array (
				"media_id" => $media_id,
				"env_type" => $type,
				"env_id" => $env_id 
		);
		$this->db->insert ( "media_env", $data );
	}
	public function delete_media_env($media_id) {
		$sql = "delete from  media_env where media_id ='$media_id'";
		$this->db->query ( $sql );
	}
	public function query_media_env_list($media_id) {
		$sql = "select * from media_env where media_id ='$media_id'";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function add_num($env_type, $env_id, $media_type) {
		if ($media_type == "led") {
			$sql = "update env_urban_$env_type set led_billboard_num =led_billboard_num+1 where env_id ='$env_id'";
			$this->db->query ( $sql );
		} else {
			$sql = "update env_urban_$env_type set common_billboard_num =common_billboard_num+1 where env_id ='$env_id'";
			$this->db->query ( $sql );
		}
	}
	public function add_env_num($env_id, $env_type) {
		if ($env_id != "" && $env_id != 0) {
			if ($env_type == "highway") {
				$sql = "update env_highway set billboard_num =billboard_num+1 where highway_id ='$env_id'";
			}
			if ($env_type == "section") {
				$sql = "update env_highway_section set billboard_num =billboard_num+1 where section_id ='$env_id'";
			}
			if ($env_type == "point") {
				$sql = "update env_highway_point set billboard_num =billboard_num+1 where point_id ='$env_id'";
			}
			if ($env_type == "airport") {
				$sql = "update env_airport set billboard_num =billboard_num+1 where airport_id ='$env_id'";
			}
			if (isset ( $sql )) {
				$this->db->query ( $sql );
			}
		}
	}
	public function query_media_list($user_id, $start, $limit, $search) {
		if ($user_id == 1) {
			$sql = "select
					media_id,
					location,
					media_type,
					province,
					city,
					region,
					r1.region_name as province_name,
					r2.region_name as city_name,
					r3.region_name as region_name
			from 	
					media
					inner join sys_region as r1 on media.province =r1.region_id
					left join sys_region as r2 on media.city =r2.region_id
					left join sys_region as r3 on media.region =r3.region_id
			where 1=1 ";
		} else {
			$sql = "select
						media_id,
						location,
						media_type,
						media.province,
						media.city,
						media.region,
						r1.region_name as province_name,
						r2.region_name as city_name,
						r3.region_name as region_name
				from 	
						media inner join adm_user_region as aur on media.province=aur.region_id
						inner join sys_region as r1 on media.province =r1.region_id
						left join sys_region as r2 on media.city =r2.region_id
						left join sys_region as r3 on media.region =r3.region_id
				where aur.user_id ='$user_id'";
		}
		$search_path = "";
		if ($search ['region']) {
			$region = $this->db->escape ( $search ['region'] );
			$search_path = " and  region =$region";
		} elseif ($search ['city']) {
			$city = $this->db->escape ( $search ['city'] );
			$search_path = " and  city =$city";
		} elseif ($search ['province']) {
			$province = $this->db->escape ( $search ['province'] );
			$search_path = " and  province =$province";
		}
		if ($search ['media_type'] != "") {
			$media_type = $this->db->escape ( $search ['media_type'] );
			$search_path .= " and media_type = $media_type";
		}
		if ($search ['media_type_id'] != "") {
			$province = $this->db->escape ( $search ['media_type_id'] );
			$search_path .= " and media_type_id = $media_type_id";
		}
		if ($search ['keyword'] != "") {
			$keyword = $this->db->escape_like_str ( $search ['keyword'] );
			$search_path .= " and location like '%$keyword%'";
		}
		$sql = $sql . $search_path;
		$sql = $sql . " order by media_id desc limit $start,$limit";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function query_media_num($user_id, $search) {
		if ($user_id == 1) {
			$sql = "select count(media_id) as num from media where 1=1 ";
		} else {
			$sql = "select count(media_id) as num 
					from 	media 
							inner join adm_user_region as aur on media.province=aur.region_id
					where user_id ='$user_id'";
		}
		$search_path = "";
		if ($search ['region']) {
			$region = $this->db->escape ( $search ['region'] );
			$search_path = " and  region =$region";
		} elseif ($search ['city']) {
			$city = $this->db->escape ( $search ['city'] );
			$search_path = " and  city =$city";
		} elseif ($search ['province']) {
			$province = $this->db->escape ( $search ['province'] );
			$search_path = " and  province =$province";
		}
		if ($search ['media_type'] != "") {
			$media_type = $this->db->escape ( $search ['media_type'] );
			$search_path .= " and media_type = $media_type";
		}
		if ($search ['media_type_id'] != "") {
			$province = $this->db->escape ( $search ['media_type_id'] );
			$search_path .= " and media_type_id = $media_type_id";
		}
		if ($search ['keyword'] != "") {
			$keyword = $this->db->escape_like_str ( $search ['keyword'] );
			$search_path .= " and location like '%$keyword%'";
		}
		$sql = $sql . $search_path;
		$query = $this->db->query ( $sql );
		if ($query) {
			$row = $query->row_array ();
			return $row ['num'];
		}
	}
	public function query_media($media_id) {
		$sql = "	select *
				from media
				where media_id='$media_id'
				limit 1";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->row_array ();
		}
	}
	public function query_media_detail($media_id) {
		$sql = "select
					m.*,
					r1.region_name as province_name,
					r2.region_name as city_name,
					r3.region_name as region_name,
					r3.region_type,
					sc.crafts_name,
					scc.corner_case_name,
					smt.media_type_name,
					sma.media_area_name,
					vv.company_name
				from
					media as m
					inner join sys_region as r1 on m.province =r1.region_id
					left join sys_region as r2 on m.city =r2.region_id
					left join sys_region as r3 on m.region =r3.region_id
					left join ven_vendor as vv on m.vendor =vv.vendor_id
					left join sys_crafts as sc on m.crafts =sc.crafts_id
					left join sys_corner_case  as scc on m.corner_case =scc.corner_case_id
					left join sys_media_type as smt on m.media_type_id =smt.media_type_id
					left join sys_media_area as sma on m.media_area =sma.media_area_id
				where
					m.media_id ='$media_id'
					limit 1";
		
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->row_array ();
		}
	}
	public function query_media_pic_list($media_id) {
		$sql = "select * from media_pic where media_id ='$media_id'";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function query_env_media_list($env_id, $env_type, $media_type) {
		$sql = "select media.media_id 
				from 
					media_env inner join media on media_env.media_id =media.media_id
				where
					env_id ='$env_id' 
					and env_type ='$env_type'
					and media_type ='$media_type'";
		
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function delete_excellent_media($env_id, $env_type) {
		$sql = "delete from  env_excellent_media where env_id ='$env_id' and env_type='$env_type'";
		$this->db->query ( $sql );
	}
	public function save_assgin_excellent_media($env_id, $env_type, $media_id, $media_type) {
		$data = array (
				"env_id" => $env_id,
				"env_type" => $env_type,
				"media_id" => $media_id,
				"media_type" => $media_type 
		);
		$this->db->insert ( "env_excellent_media", $data );
	}
	public function query_env_excellent_media_list($env_id, $env_type, $media_type) {
		$sql = "select media_id from env_excellent_media where env_id ='$env_id' and env_type ='$env_type' and media_type ='$media_type'";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function search_my_media_list($user_name, $user_id, $keyword) {
		$keyword = $this->db->escape_like_str ( $keyword );
		if ($keyword != "") {
			if ($user_id == 1) {
				$sql = "
					select
						media_id as id,
						location
					from
						media 
					where
					(media_id like '%$keyword%' or location like '%$keyword%')
					order by media_id desc limit 100";
			} else {
				$sql = "
					select
						media_id as id,
						location
					from
						media inner join adm_user_region as aur on media.province=aur.region_id 
					where aur.user_id ='$user_id'
						and  (media_id like '%$keyword%' or location like '%$keyword%')
						order by media_id desc limit 100";
			}
		} else {
			return array ();
		}
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function delete_media($media_id) {
		$media_id = $this->db->escape ( $media_id );
		$sql = "delete  from media where media_id =$media_id";
		$query = $this->db->query ( $sql );
	}
	public function mobile_query_nearby_media($user_id, $longitude, $latitude) {
		if ($user_id == 1) {
			$sql = "select
					media_id,
					location,
					media_type,
					province,
					city,
					region,
					r1.region_name as province_name,
					r2.region_name as city_name,
					r3.region_name as region_name,
					ABS(longitude -'$longitude') as lo_number,
					ABS(latitude -'$latitude') as la_number
			from
					media
					inner join sys_region as r1 on media.province =r1.region_id
					left join sys_region as r2 on media.city =r2.region_id
					left join sys_region as r3 on media.region =r3.region_id
			where 1=1 ";
		} else {
			$sql = "select
							media_id,
							location,
							media_type,
							media.province,
							media.city,
							media.region,
							r1.region_name as province_name,
							r2.region_name as city_name,
							r3.region_name as region_name,
							ABS(longitude -'$longitude') as lo_number,
							ABS(latitude -'$latitude') as la_number
					from
							media inner join adm_user_region as aur on media.province=aur.region_id
							inner join sys_region as r1 on media.province =r1.region_id
							left join sys_region as r2 on media.city =r2.region_id
							left join sys_region as r3 on media.region =r3.region_id
					where aur.user_id ='$user_id' ";
		}
		$sql = $sql . " order by (lo_number+la_number) asc limit 0,20";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
}